package org.example.myspring02.SqlMapper;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@Component
@RestController
public class SQLController {
    @Autowired
    private SQLconnect sqlconnect;
    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;
    @Autowired
    private SQlsentence sql;

    public void insert(String name, int age, String gender, String address) throws SQLException {
        try {
            connection = sqlconnect.getConnection();
            preparedStatement = connection.prepareStatement(sql.insert);
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.setString(3, gender);
            preparedStatement.setString(4, address);
            int i = preparedStatement.executeUpdate();
            if (i == 1) {
                System.out.println("插入成功");
            } else {
                System.out.println("插入失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            sqlconnect.close(connection, preparedStatement, resultSet);
        }
    }

    public void updateById(String name, int age, String gender, String address, int id) throws SQLException {
        try {
            connection = sqlconnect.getConnection();
            preparedStatement = connection.prepareStatement(sql.update_by_id);
            if (name != null) preparedStatement.setString(1, name);
            if (age != 0) preparedStatement.setInt(2, age);
            if (gender != null) preparedStatement.setString(3, gender);
            if (address != null) preparedStatement.setString(4, address);
            if (id != 0) preparedStatement.setInt(5, id);
            int i = preparedStatement.executeUpdate();
            if (i == 1) {
                System.out.println("更新成功");
            } else {
                System.out.println("更新失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            sqlconnect.close(connection, preparedStatement, resultSet);
        }
    }

    public void deleteById(int id) throws SQLException {
        try {
            connection = sqlconnect.getConnection();
            preparedStatement = connection.prepareStatement(sql.delete_by_id);
            preparedStatement.setInt(1, id);
            int i = preparedStatement.executeUpdate();
            if (i == 1) {
                System.out.println("删除成功");
            } else {
                System.out.println("删除失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            sqlconnect.close(connection, preparedStatement, resultSet);
        }
    }

    public String selectById(int id) throws SQLException {
        try {
            connection = sqlconnect.getConnection();
            preparedStatement = connection.prepareStatement(sql.select_by_id);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");
                String address = resultSet.getString("address");
                return "name:" + name + " age:" + age + " gender:" + gender + " address:" + address;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            sqlconnect.close(connection, preparedStatement, resultSet);
        }
        return "未找到该id";
    }
}
